<?php
// 公共工具函数
// 注意：calculate_duration()函数已在db_connect.php中定义，此处不再重复定义

// 验证并格式化日期函数
function validateAndFormatDate($raw_date) {
    // 尝试解析日期
    $date = date_create_from_format('Y-m-d', $raw_date);
    if ($date === false) {
        // 尝试其他常见格式
        $date = date_create_from_format('Y/m/d', $raw_date);
        if ($date === false) {
            $date = date_create_from_format('d/m/Y', $raw_date);
            if ($date === false) {
                $date = date_create_from_format('m/d/Y', $raw_date);
                if ($date === false) {
                    // 尝试识别自然语言日期
                    $date = date_create($raw_date);
                }
            }
        }
    }
    
    // 如果成功解析日期，返回标准格式
    if ($date !== false) {
        return $date->format('Y-m-d');
    }
    
    // 如果无法解析，返回false
    return false;
}

// 处理导入CSV功能
// 改进的编码检测和转换函数
function detectAndConvertEncoding($text) {
    // 检测UTF-8 BOM
    if (substr($text, 0, 3) === "\xEF\xBB\xBF") {
        $text = substr($text, 3);
    }
    
    // 尝试检测并转换常见编码
    $encoding = mb_detect_encoding($text, ['UTF-8', 'GBK', 'GB2312', 'CP936', 'ISO-8859-1'], true);
    if ($encoding && $encoding !== 'UTF-8') {
        $text = mb_convert_encoding($text, 'UTF-8', $encoding);
    }
    
    return $text;
}

function handleImportCSV($conn) {
    $error = '';
    $success = '';
    $error_details = []; // 存储详细的失败原因
    
    if (isset($_FILES['csv_file']) && $_FILES['csv_file']['error'] == 0) {
        // 检查文件类型
        $allowed_extensions = ['csv', 'xlsx', 'xls'];
        $file_extension = strtolower(pathinfo($_FILES['csv_file']['name'], PATHINFO_EXTENSION));
        
        if (!in_array($file_extension, $allowed_extensions)) {
            $error = '不支持的文件类型。请上传CSV或Excel文件。';
        } else if ($_FILES['csv_file']['size'] > 5 * 1024 * 1024) { // 5MB限制
            $error = '文件大小超过限制。最大允许5MB。';
        } else {
            // 临时文件路径
            $tmp_file = $_FILES['csv_file']['tmp_name'];
            
            if ($file_extension == 'csv') {
                // 处理CSV文件
                // 使用更健壮的方法处理中文编码
                $file_content = file_get_contents($tmp_file);
                $file_content = detectAndConvertEncoding($file_content);
                
                // 创建临时文件存储转换后的内容
                $temp_csv = tempnam(sys_get_temp_dir(), 'csv_');
                file_put_contents($temp_csv, $file_content);
                
                // 使用正确的编码打开CSV文件
                if (($handle = fopen($temp_csv, 'r')) !== false) {
                    // fgetcsv在现代PHP版本中默认能正确处理不同的行结束符
                    
                    // 读取表头
                    $headers = fgetcsv($handle);
                    if ($headers === false) {
                        $error = '无法读取CSV文件的表头';
                        fclose($handle);
                        if (file_exists($temp_csv)) {
                            unlink($temp_csv);
                        }
                        return ['error' => $error, 'success' => $success];
                    }
                    
                    // 创建字段名到索引的映射
                    $field_map = [];
                    foreach ($headers as $index => $header) {
                        $normalized_header = trim($header);
                        $field_map[$normalized_header] = $index;
                    }
                    
                    // 统计成功和失败的记录数
                    $success_count = 0;
                    $error_count = 0;
                    $record_line = 1; // 记录行号（从1开始，已读取表头）
                
                    // 准备插入语句（使用外键关联）
                    $stmt = $conn->prepare("INSERT INTO exam_records 
                        (exam_date, question_type, exercise_set_id, chapter_id, question_number, notes, error_reason, reflection, start_time, end_time, duration, correctness) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    
                    if ($stmt) {
                        $actual_record_count = 0; // 实际有效数据行计数
                        while (($data = fgetcsv($handle)) !== false) {
                            $record_line++;
                            
                            // 跳过空行或只包含空白字符的行
                            if (empty(array_filter($data, function($value) { return trim($value) !== ''; }))) {
                                continue;
                            }
                            
                            $actual_record_count++;
                            
                            // 验证必填字段是否存在于表头中
                            // 支持两种格式：1. 旧格式（单一'章节'列） 2. 新格式（'习题集'和'章节'两列）
                            $new_format_fields = ['日期', '做题方式', '习题集', '章节', '例题号', '开始时间', '结束时间'];
                            $old_format_fields = ['日期', '做题方式', '章节', '例题号', '开始时间', '结束时间'];
                            
                            // 检查是否为新格式（同时包含'习题集'和'章节'）
                            $is_new_format = isset($field_map['习题集']) && isset($field_map['章节']);
                            // 检查是否为旧格式（只包含'章节'）
                            $is_old_format = isset($field_map['章节']) && !isset($field_map['习题集']);
                            
                            // 根据格式选择需要验证的字段
                            if ($is_new_format) {
                                $required_fields = $new_format_fields;
                            } elseif ($is_old_format) {
                                $required_fields = $old_format_fields;
                            } else {
                                // 既不是新格式也不是旧格式
                                $error_count++;
                                $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：CSV格式错误，缺少必需的'章节'字段，或'习题集'和'章节'字段组合";
                                continue;
                            }
                            
                            $missing_in_header = array_diff($required_fields, array_keys($field_map));
                            if (!empty($missing_in_header)) {
                                $error_count++;
                                $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：CSV表头缺少必需字段：" . implode('、', $missing_in_header);
                                continue;
                            }
                            
                            // 验证必填字段的值是否为空
                            $missing_values = [];
                            foreach ($required_fields as $field) {
                                $index = $field_map[$field];
                                if (!isset($data[$index]) || empty(trim($data[$index]))) {
                                    $missing_values[] = $field;
                                }
                            }
                            
                            if (!empty($missing_values)) {
                                $error_count++;
                                $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：缺少必填字段值：" . implode('、', $missing_values);
                                continue;
                            }
                            
                            // 根据表头获取字段值
                            $raw_date = $data[$field_map['日期']];
                            $question_type = $data[$field_map['做题方式']];
                            $question_number = $data[$field_map['例题号']];
                            
                            // 获取或创建默认习题集和章节
                            $exercise_set_id = 1; // 默认习题集ID
                            $chapter_id = null;
                            
                            if ($is_new_format) {
                                // 新格式：分别获取习题集和章节
                                $exercise_set_name = $data[$field_map['习题集']];
                                $chapter_name = $data[$field_map['章节']];
                                
                                // 尝试根据习题集名称获取exercise_set_id
                                $exercise_set_query = $conn->prepare("SELECT id FROM exercise_sets WHERE name = ?");
                                $exercise_set_query->bind_param("s", $exercise_set_name);
                                $exercise_set_query->execute();
                                $exercise_set_result = $exercise_set_query->get_result();
                                if ($exercise_set_result && $exercise_set_row = $exercise_set_result->fetch_assoc()) {
                                    $exercise_set_id = $exercise_set_row['id'];
                                } else {
                                    $error_count++;
                                    $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：习题集'{$exercise_set_name}'不存在，请先创建习题集";
                                    continue;
                                }
                                $exercise_set_query->close();
                            } else {
                                // 旧格式：只有章节字段
                                $chapter_name = $data[$field_map['章节']];
                            }
                            
                            // 尝试根据章节名称获取chapter_id
                            $chapter_query = $conn->prepare("SELECT id FROM chapters WHERE name = ?");
                            $chapter_query->bind_param("s", $chapter_name);
                            $chapter_query->execute();
                            $chapter_result = $chapter_query->get_result();
                            if ($chapter_result && $chapter_row = $chapter_result->fetch_assoc()) {
                                $chapter_id = $chapter_row['id'];
                            } else {
                                // 如果章节不存在，可以选择跳过或创建新章节
                                $error_count++;
                                $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：章节'{$chapter_name}'不存在，请先创建章节";
                                continue;
                            }
                            $chapter_query->close();
                            $start_time = $data[$field_map['开始时间']];
                            $end_time = $data[$field_map['结束时间']];
                            
                            // 获取可选字段（如果存在）
                            $notes = isset($field_map['备注']) && isset($data[$field_map['备注']]) ? $data[$field_map['备注']] : '';
                            $error_reason = isset($field_map['错因']) && isset($data[$field_map['错因']]) ? $data[$field_map['错因']] : '';
                            $reflection = isset($field_map['反思']) && isset($data[$field_map['反思']]) ? $data[$field_map['反思']] : '';
                            $duration = isset($field_map['耗时']) && isset($data[$field_map['耗时']]) && !empty(trim($data[$field_map['耗时']])) ? $data[$field_map['耗时']] : calculate_duration($start_time, $end_time);
                            $correctness = isset($field_map['正确情况']) && isset($data[$field_map['正确情况']]) ? $data[$field_map['正确情况']] : '未批改';
                            
                            // 日期格式验证和转换
                            $exam_date = validateAndFormatDate($raw_date);
                            if ($exam_date === false) {
                                $error_count++;
                                $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：日期格式错误（'{$raw_date}' 无法识别为有效日期）";
                                continue;
                            }
                            
                            // 绑定参数 - 确保类型定义字符串长度与参数数量完全匹配
                            $stmt->bind_param(
                                "ssiissssssss",
                                $exam_date,
                                $question_type,
                                $exercise_set_id,
                                $chapter_id,
                                $question_number,
                                $notes,
                                $error_reason,
                                $reflection,
                                $start_time,
                                $end_time,
                                $duration,
                                $correctness
                            );
                            
                            if ($stmt->execute()) {
                                $success_count++;
                            } else {
                                $error_count++;
                                $error_details[] = "第{$actual_record_count}行（文件行号：{$record_line}）：数据库插入失败：" . $stmt->error;
                            }
                        }
                        
                        $stmt->close();
                        
                        // 构建成功消息，包含详细失败原因（如果有）
                        $success = "导入完成。成功：$success_count 条记录";
                        if ($error_count > 0) {
                            $success .= "，失败：$error_count 条记录";
                            // 如果有失败记录，将失败原因添加到错误信息中
                            if (!empty($error_details)) {
                                $error = "导入部分失败。详细失败原因：<br>" . implode("<br>", $error_details);
                            }
                        } else if ($actual_record_count === 0) {
                            $error = "未找到有效数据行，请检查CSV文件格式";
                        }
                        } else {
                            $error = '数据库预处理语句错误：' . $conn->error;
                        }
                        
                        fclose($handle);
                        // 删除临时CSV文件
                        if (file_exists($temp_csv)) {
                            unlink($temp_csv);
                        }
                    } else {
                        $error = '无法打开上传的文件。';
                        // 清理临时文件
                        if (isset($temp_csv) && file_exists($temp_csv)) {
                            unlink($temp_csv);
                        }
                    }
            } else {
                // 对于Excel文件，我们提示用户需要转换为CSV格式
                $error = '请先将Excel文件转换为CSV格式再上传。';
            }
            
            // 删除原始临时文件
            if (file_exists($tmp_file)) {
                unlink($tmp_file);
            }
        }
    }
    
    return ['error' => $error, 'success' => $success];
}

// 获取唯一值用于筛选下拉菜单
function get_unique_values($conn, $column) {
    $sql = "SELECT DISTINCT $column FROM exam_records WHERE $column != '' ORDER BY $column";
    $result = $conn->query($sql);
    $values = [];
    while ($row = $result->fetch_assoc()) {
        $values[] = $row[$column];
    }
    return $values;
}

// 获取用户使用最多的做题方式
function get_most_used_question_type($conn) {
    $sql = "SELECT question_type, COUNT(*) as count FROM exam_records WHERE question_type != '' GROUP BY question_type ORDER BY count DESC LIMIT 1";
    $result = $conn->query($sql);
    if ($row = $result->fetch_assoc()) {
        return $row['question_type'];
    }
    return '';
}

// 获取最新输入的章节
function get_latest_chapter($conn) {
    // 使用JOIN查询获取最新记录的章节名称
    $sql = "SELECT c.name FROM exam_records er 
            JOIN chapters c ON er.chapter_id = c.id 
            WHERE er.chapter_id IS NOT NULL 
            ORDER BY er.id DESC LIMIT 1";
    $result = $conn->query($sql);
    if ($row = $result->fetch_assoc()) {
        return $row['name'];
    }
    return '';
}

// 获取最新输入的例题号
function get_latest_question_number($conn) {
    $sql = "SELECT question_number FROM exam_records WHERE question_number != '' ORDER BY id DESC LIMIT 1";
    $result = $conn->query($sql);
    if ($row = $result->fetch_assoc()) {
        return $row['question_number'];
    }
    return '';
}

// 获取最新输入的习题集
function get_latest_exercise_set($conn) {
    $sql = "SELECT exercise_set_id FROM exam_records WHERE exercise_set_id IS NOT NULL ORDER BY id DESC LIMIT 1";
    $result = $conn->query($sql);
    if ($row = $result->fetch_assoc()) {
        return $row['exercise_set_id'];
    }
    return null;
}

// 处理导出Excel功能
function handleExportExcel($conn) {
    if (isset($_GET['export'])) {
        // 构建筛选查询（如果有筛选条件）
        $filter_result = buildFilterQuery($conn);
        $filter_conditions = $filter_result['conditions'];
        $params = $filter_result['params'];
        $types = $filter_result['types'];
        
        // 设置响应头，告诉浏览器这是一个CSV文件
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="exam_records_'.date('Ymd_His').'.csv"');
        
        // 打开输出流
        $output = fopen('php://output', 'w');
        
        // 添加BOM以支持Excel中的中文
        fwrite($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
        
        // 写入表头 - 将习题集和章节分开为独立列
        fputcsv($output, array('ID', '日期', '做题方式', '习题集', '章节', '例题号', '开始时间', '结束时间', '耗时(分钟)', '正确情况', '备注', '错因', '反思'));
        
        // 查询数据（连接习题集和章节表）
        $sql = "SELECT r.*, e.name AS exercise_set_name, c.name AS chapter_name FROM exam_records r 
                LEFT JOIN exercise_sets e ON r.exercise_set_id = e.id 
                LEFT JOIN chapters c ON r.chapter_id = c.id";
        if (!empty($filter_conditions)) {
            $sql .= " WHERE " . implode(" AND ", $filter_conditions);
        }
        $sql .= " ORDER BY r.exam_date DESC, r.start_time DESC";
        
        $stmt = $conn->prepare($sql);
        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        
        // 写入数据行
        while ($row = $result->fetch_assoc()) {
            // 分别输出习题集和章节，不再合并
            fputcsv($output, array(
                $row['id'],
                $row['exam_date'],
                $row['question_type'],
                $row['exercise_set_name'] ?? '', // 习题集列
                $row['chapter_name'] ?? '',      // 章节列
                $row['question_number'],
                $row['start_time'],
                $row['end_time'],
                $row['duration'],
                $row['correctness'],
                $row['notes'],
                $row['error_reason'],
                $row['reflection']
            ));
        }
        
        // 关闭资源
        $stmt->close();
        fclose($output);
        exit;
    }
}

// 渲染Tabbar组件
function renderTabbar($active_tab) {
    $tabs = [
        ['id' => 'add', 'name' => '添加记录'],
        ['id' => 'list', 'name' => '数据管理'], // 合并筛选和记录列表为一个标签
        ['id' => 'exercise_sets', 'name' => '习题集管理'] // 添加习题集管理标签
    ];
    
    $tabHtml = '<div class="tabbar mb-4">
        <div class="tabs d-flex gap-3" style="background-color: var(--slate-100); padding: 0.5rem; border-radius: 12px;">';
    
    foreach ($tabs as $tab) {
        $activeClass = ($active_tab == $tab['id']) ? ' active' : '';
        // 所有标签都使用index.php?tab参数
        $href = 'index.php?tab=' . $tab['id'];
        // 为标签项添加基本样式
        $tabStyle = 'padding: 0.75rem 1.5rem; border-radius: 8px; text-decoration: none; font-weight: 500; transition: all 0.2s ease;';
        // 根据激活状态添加不同的颜色样式
        if ($active_tab == $tab['id']) {
            $tabStyle .= ' background-color: #1e293b; color: #f8fafc;';
        } else {
            $tabStyle .= ' background-color: transparent; color: #334155;';
        }
        $tabHtml .= '            <a href="' . $href . '" class="tab-item' . $activeClass . '" style="' . $tabStyle . '">';
        $tabHtml .= $tab['name'] . '</a>';
    }
    
    $tabHtml .= '        </div>
    </div>';
    
    return $tabHtml;
}

// 计算做题耗时（分钟）
function calculate_duration($start_time, $end_time) {
    $start = strtotime($start_time);
    $end = strtotime($end_time);
    
    // 如果结束时间小于开始时间，假设是跨天，但这里简单处理为结束时间加一天
    if ($end < $start) {
        $end += 86400; // 24小时的秒数
    }
    
    $diff_in_seconds = $end - $start;
    return round($diff_in_seconds / 60); // 转换为分钟并四舍五入
}

// 处理添加记录后重定向（避免刷新重复提交）
function handleAddRecordRedirection($conn) {
    if (isset($_POST['add_record'])) {
        // 获取表单数据
        $exam_date = $_POST['exam_date'];
        $question_type = $_POST['question_type'];
        $exercise_set_id = $_POST['exercise_set_id'];
        $chapter_id = $_POST['chapter_id'];
        $question_number = $_POST['question_number'];
        $start_time = $_POST['start_time'];
        $end_time = $_POST['end_time'];
        $correctness = isset($_POST['correctness']) ? $_POST['correctness'] : '未批改';
        $notes = isset($_POST['notes']) ? $_POST['notes'] : '';
        $error_reason = isset($_POST['error_reason']) ? $_POST['error_reason'] : '';
        $reflection = isset($_POST['reflection']) ? $_POST['reflection'] : '';
        
        // 服务器端再次检查是否存在重复记录
        $is_duplicate = checkDuplicateRecord($conn, $exam_date, $question_type, $exercise_set_id, $chapter_id, $question_number, $start_time, $end_time);
        
        if ($is_duplicate) {
            // 如果存在重复记录，重定向回添加页面并显示错误信息
            header("Location: index.php?tab=add&duplicate=true");
            exit();
        }
        
        // 计算做题耗时
        $duration = calculate_duration($start_time, $end_time);
        
        // 插入记录到数据库（包含错因和反思字段）
        $sql = "INSERT INTO exam_records (exam_date, question_type, exercise_set_id, chapter_id, question_number, start_time, end_time, duration, correctness, notes, error_reason, reflection) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sssssssissss", $exam_date, $question_type, $exercise_set_id, $chapter_id, $question_number, $start_time, $end_time, $duration, $correctness, $notes, $error_reason, $reflection);
        $stmt->execute();
        $stmt->close();
        
        // 设置成功消息参数，用于显示模态框
        header("Location: index.php?tab=add&added=true");
        exit();
    }
}

// 处理更新记录后重定向
function handleUpdateRecordRedirection($conn) {
    if (isset($_POST['update_record'])) {
        // 获取表单数据
        $id = $_POST['id'];
        $exam_date = $_POST['exam_date'];
        $question_type = $_POST['question_type'];
        $exercise_set_id = $_POST['exercise_set_id'];
        $chapter_id = $_POST['chapter_id'];
        $question_number = $_POST['question_number'];
        $start_time = $_POST['start_time'];
        $end_time = $_POST['end_time'];
        $correctness = isset($_POST['correctness']) ? $_POST['correctness'] : '未批改';
        $notes = isset($_POST['notes']) ? $_POST['notes'] : '';
        $error_reason = isset($_POST['error_reason']) ? $_POST['error_reason'] : '';
        $reflection = isset($_POST['reflection']) ? $_POST['reflection'] : '';
        
        // 计算做题耗时
        $duration = calculate_duration($start_time, $end_time);
        
        // 更新记录
        $sql = "UPDATE exam_records SET exam_date=?, question_type=?, exercise_set_id=?, chapter_id=?, question_number=?, start_time=?, end_time=?, duration=?, correctness=?, notes=?, error_reason=?, reflection=? WHERE id=?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sssssssissssi", $exam_date, $question_type, $exercise_set_id, $chapter_id, $question_number, $start_time, $end_time, $duration, $correctness, $notes, $error_reason, $reflection, $id);
        $stmt->execute();
        $stmt->close();
        
        // 重定向到记录列表
        header("Location: index.php?tab=list");
        exit();
    }
}

// 处理删除记录后重定向
function handleDeleteRecordRedirection($conn) {
    if (isset($_GET['delete'])) {
        $id = $_GET['delete'];
        
        // 删除记录
        $sql = "DELETE FROM exam_records WHERE id=?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $stmt->close();
        
        // 重定向到记录列表
        header("Location: index.php?tab=list");
        exit();
    }
}

// 检查是否存在重复记录
function checkDuplicateRecord($conn, $exam_date, $question_type, $exercise_set_id, $chapter_id, $question_number, $start_time, $end_time) {
    $count = 0; // 初始化变量
    // 准备SQL查询，检查是否存在相同日期、做题方式、习题集、章节、题号和做题时间的记录
    $sql = "SELECT COUNT(*) FROM exam_records WHERE exam_date = ? AND question_type = ? AND exercise_set_id = ? AND chapter_id = ? AND question_number = ? AND start_time = ? AND end_time = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sssssss", $exam_date, $question_type, $exercise_set_id, $chapter_id, $question_number, $start_time, $end_time);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
    
    // 如果记录数大于0，则表示存在重复记录
    return $count > 0;
}